package ch.sv7.tool.dbmanager.db.dialect.sybase;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import ch.sv7.tool.dbmanager.Context;
import ch.sv7.tool.dbmanager.db.ColumnSchema;
import ch.sv7.tool.dbmanager.db.ConstraintSchema;
import ch.sv7.tool.dbmanager.db.ForeignKeySchema;
import ch.sv7.tool.dbmanager.db.IndexSchema;
import ch.sv7.tool.dbmanager.db.TableSchema;
import ch.sv7.tool.dbmanager.db.dialect.DefaultDialect;
import ch.sv7.tool.dbmanager.db.dialect.DialectException;
import ch.sv7.tool.dbmanager.types.TypesInfo;
import ch.sv7.tool.log.Logger;

/**
 * TODO: Document me!
 * 
 * @author svt
 * 
 */
public class SybaseDialect extends DefaultDialect {
	
	private static final Logger log = Logger.getLogger(SybaseDialect.class);
	public static final String CONTEXT_PROPS_PATH = "/ch/sv7/tool/dbmanager/db/sybase/sybase.properties";

	public static final String NB_PARTITION_KEY = "sybase.nbPartitions";
	
	public static final String PROPERTY_TABLE_PARTITION = "table-partition";
	public static final String PROPERTY_INDEX_PARTITION = "index-partition";
	public static final String PROPERTY_LOCKSCHEME = "lockscheme";
	public static final String PROPERTY_RESERVEDPAGEGAP = "reservedpagegap";
	
	private static final String[][] types = new String[][] 
	      { { "BIGINT", "bigint" }, 
			{ "BINARY", "binary" }, 
			{ "BIT", "bit" },
			{ "BLOB", "image" }, 
			{ "BOOLEAN", "bit" }, 
			{ "CHAR", "char" }, 
			{ "CLOB", "text" }, 
			{ "DATE", "date" },
			{ "DECIMAL", "decimal" }, 
			{ "DOUBLE", "float" }, 
			{ "FLOAT", "float" }, 
			{ "INTEGER", "int" }, 
			{ "LONGNVARCHAR", "text" },
			{ "LONGVARBINARY", "image" }, 
			{ "LONGVARCHAR", "text" }, 
			{ "NCHAR", "char" }, 
			{ "NCLOB", "text" },
			{ "NUMERIC", "numeric" }, 
			{ "NVARCHAR", "varchar" }, 
			{ "REAL", "real" }, 
			{ "SMALLINT", "smallint" },
			{ "TIME", "time" },
			{ "TIMESTAMP", "datetime" },
			{ "TINYINT", "tinyint" },
			{ "TIMESTAMP", "datetime" },
			{ "VARBINARY", "varbinary" },
			{ "VARCHAR", "varchar" }
			};
		
	/*
	 * Unmapped:
	 * ARRAY
	 * DATALINK
	 * DISTINCT
	 * JAVA_OBJECT
	 * NULL
	 * OTHER
	 * REF
	 * ROWID
	 * SQLXML
	 * STRUCT
	 */

	private Map typesMap;
	
	public String getName(){
		return "sybase";
	}
	
	/**
	 * @return the types
	 */
	public String[][] getTypes() {
		return types;
	}

	public int getPartitionNumber(TableSchema table){
		String nbStr = table.getProperties().getProperty(NB_PARTITION_KEY);
		if (nbStr != null && nbStr.trim().length() > 0) {
			try {
				int result = Integer.parseInt(nbStr);
				return result;
			} catch (NumberFormatException e) {
				// ignore
			}
		}
		return -1;
	}
	
	public void setPartitionNumber(int nb, TableSchema table) {
		table.getProperties().setProperty(NB_PARTITION_KEY, Integer.toString(nb));
	}
	/**
	 * @param typesInfo
	 */
	public SybaseDialect(TypesInfo typesInfo) {
		super(typesInfo);
		initTypes(getTypes());
	}

	protected void initTypes(String[][] types) {
		if (typesMap == null){
			typesMap = new HashMap();
			for (int i=0; i< types.length ; i++) {
				typesMap.put(types[i][0], types[i][1]);
			}
		}		
	}
	
	public String getDefaultTypeName(String jdbcTypeName) {
		String result = (String)typesMap.get(jdbcTypeName.toUpperCase());
		if (result == null) {
			return jdbcTypeName;
		}
		return result;
	}
	
	public String getUpdateColumnDDL(ColumnSchema schema, boolean ignoreDefault) throws DialectException {
		StringBuffer ddl = new StringBuffer();
		ddl.append("ALTER TABLE " + schema.getTable().getName());
		ddl.append(" MODIFY ");
		ddl.append(getColumnDDL(schema, true));
		ddl.append(getStatementDelimiter() + NL);

		// Handle default value (sybase specific replace syntax)
		ddl.append("ALTER TABLE " + schema.getTable().getName());
		ddl.append(" REPLACE ");
		ddl.append(schema.getName());
		ddl.append(" DEFAULT ");
		if (schema.getType().hasDefaultValue()){
			ddl.append(schema.getType().getDefaultValue());
		} else {
			ddl.append("NULL");
		}
		ddl.append(getStatementDelimiter() + NL);
		return ddl.toString();
	}
	
	public String getAddIndexDDL(IndexSchema index) {
		String ddl = super.getAddIndexDDL(index);
		String partition = getProperty(index.isPrimaryKey() ? PROPERTY_TABLE_PARTITION : PROPERTY_INDEX_PARTITION);
		if (partition != null) {
			int pos = ddl.lastIndexOf(getStatementDelimiter());
			if (pos > 0) {
				return ddl.substring(0, pos) + " " + partition + ddl.substring(pos);
			} else {
				log.error("Unable to find statement delimiter in " + ddl + ", ignoring partition info");
				return ddl;
			}
		} else {
			return ddl;
		}
	}


	public String getTableDDL(TableSchema schema) throws DialectException {
		StringBuffer ddl = new StringBuffer();
		ddl.append(super.getTableDDL(schema));
		
		String lockScheme = getProperty(PROPERTY_LOCKSCHEME);
		if (lockScheme != null) {
			ddl.append(lockScheme + " ");
		}
		
		String reservedPageGap = getProperty(PROPERTY_RESERVEDPAGEGAP);
		if (reservedPageGap != null) {
			ddl.append(reservedPageGap + " ");
		}
		
		String partition = getProperty(PROPERTY_TABLE_PARTITION);
		if (partition != null) {
			ddl.append(partition);
		}
		return ddl.toString();
	}

	public String getRenameColumnDDL(ColumnSchema schema, String newName) {
		StringBuffer ddl = new StringBuffer();
		ddl.append("sp_rename '" + schema.getTable().getName() 
				+ "." + schema.getName() +"', " + newName + ", 'column'");
		
		ddl.append(getStatementDelimiter() + NL);
		return ddl.toString();
	}
	
	public String getRenameIndexDDL(IndexSchema schema, String newName) {
		StringBuffer ddl = new StringBuffer();
		ddl.append("sp_rename '" + schema.getTableSchema().getName() 
				+ "." + schema.getName() +"', " + newName + ", 'index'");
		
		ddl.append(getStatementDelimiter() + NL);
		return ddl.toString();
	}
	
	public String getRenameForeignKeyDDL(ForeignKeySchema schema, String newName) {
		StringBuffer ddl = new StringBuffer();
		ddl.append("sp_rename " + schema.getName() 
				+", " + newName);
		
		ddl.append(getStatementDelimiter() + NL);
		return ddl.toString();
	}
	
	public char getDataTypeConversion(String fromType, String toType) {
		return SybaseDataTypeConversion.getConversionType(fromType, toType);
	}

	public void readDatabaseSpecificInformation(TableSchema tableSchema, Connection conn) throws DialectException {
		readConstraints(tableSchema, conn);
		int nbPart = getPartitionNumber(tableSchema, conn);
		setPartitionNumber(nbPart, tableSchema);
	}

	public void readConstraints(TableSchema tableSchema, Connection conn) throws DialectException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("sp_helpconstraint " + tableSchema.getName());
			while (rs.next()) {
				String name = rs.getString("name").trim();
				String defintion = rs.getString("definition").trim();
				String checkConstraint = "CONSTRAINT " + name + " CHECK ";
				String uniqueIndexConstraint = "UNIQUE INDEX ";
				String indexConstraint = "INDEX ";
				if (defintion.startsWith(checkConstraint)){
					defintion = defintion.substring(checkConstraint.length()).trim();
					if (defintion.startsWith("(") && defintion.endsWith(")")) {
						defintion = defintion.substring(1, defintion.length() - 1);
					}
					ConstraintSchema cs = new ConstraintSchema(tableSchema, name, defintion);
					tableSchema.getConstraints().add(cs);					
				} else if (defintion.startsWith(indexConstraint) || defintion.startsWith(uniqueIndexConstraint)){
					IndexSchema index = tableSchema.getIndexByName(name);
					if (index != null){
						index.setConstraint(true);
					}
				}
			}
		} catch (SQLException e){
			//throw new DialectException(e.getMessage(), e);
		} finally {
			try {
				if (rs != null){
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				throw new DialectException(e.getMessage(), e);
			}
			
		}
	}
	
	protected int getPartitionNumber(TableSchema tableSchema, Connection conn) throws DialectException {
		Statement stmt = null;
		ResultSet rs = null;
		int nbPartitions = -1;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(
					"select count(*) from syspartitions p where p.id =object_id('" 
					+ tableSchema.getName()
					+ "') and p.indid < 2");
			
			if (rs.next()) {
				nbPartitions = rs.getInt(1);
			}
		} catch (SQLException e){
			//throw new DialectException(e.getMessage(), e);
		} finally {
			try {
				if (rs != null){
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				throw new DialectException(e.getMessage(), e);
			}
			
		}
		return nbPartitions;
	}
	
	public String getDisableAutoIncrementDDL(ColumnSchema schema) {
		return "SET IDENTITY_UPDATE " + schema.getTable().getName() + " on" + getStatementDelimiter() + NL;
	}

	public String getEnableAutoIncrementDDL(ColumnSchema schema) {
		return "SET IDENTITY_UPDATE " + schema.getTable().getName() + " off" + getStatementDelimiter() + NL;
	}
	
	public boolean isRealError(SQLException e) {
		boolean warning = e.getClass().getName().indexOf("SybSQLWarning") > 0;
		return !warning;
	}


	public Properties getContextProperties() {
		Properties props = new Properties();
		try {
			Context.readPropertiesFromPath(props, CONTEXT_PROPS_PATH);
		} catch (IOException e) {
			log.error(e.getMessage());
		}
		return props;
	}
	
	
}
